package com.hnkywl.sts.dao.report;

import com.hnkywl.sts.dao.base.RegionForBgDao;
import com.hnkywl.sts.entity.organ.Ejzh;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.tool.hbm2x.StringUtils;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: ccr
 * Date: 14-1-17
 * Time: 上午9:45
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class EjzhmxDao extends SimpleHibernateTemplate<Ejzh, Long> {

    public Map findEjzhmx(Long gsId, Long khId, Date endDate, int pageNo, int pageSize) {
        String sqlCount ="select id as \"id\",gs as  \"gs\",kh as  \"kh\",ejzhmc as  \"ejzhmc\",zh as \"zh\"," +
                "zhye as  \"zhye\",createorgan_id as \"createorgan_id\", "+
                " createdepartment_id as \"createdepartment_id\", createuser_id as \"createuser_id\" ";
        String sql = "select ejzh.id id,gs.qc gs,kh.qc kh,ejzh.qc ejzhmc,ejzh.zh zh," +
                "sum(decode(ejzh.id,sfk.jfejzh_id,sfk.sfje,sfk.dfejzh_id,-sfk.sfje,0)) zhye, "+
                " ejzh.createorgan_id createorgan_id, ejzh.createdepartment_id createdepartment_id, ejzh.createuser_id createuser_id ";
        String from = "from t_sys_ejzh ejzh " +
                "left join t_biz_sfk sfk " +
                "on (sfk.jfejzh_id = ejzh.id and sfk.zt = 'ysh' and sfk.deleted = 0) " +
                "or (sfk.dfejzh_id = ejzh.id and sfk.zt = 'ysh' and sfk.deleted = 0)" +
                "left join t_sys_gsjg gs on ejzh.nbgs_id = gs.id  " +
                "left join t_sys_gsjg kh on ejzh.kh_id = kh.id  " +
                "where sfk.shrq < :endDate and (sfk.sfklx not in (3,4) or sfk.sfbzj <> 1) " +
                (gsId == null ? "" : "and gs.id = :gsId ") + (khId == null ? "" : "and kh.id = :khId ");
        String groupBy = "group by ejzh.id,gs.qc,kh.qc,ejzh.qc,ejzh.zh,ejzh.createorgan_id,ejzh.createdepartment_id,ejzh.createuser_id ";
        String countSql = "select count(1) from (" + sql + from + groupBy + ") d " + RegionForBgDao.SQLDataRegion("d");
        String resultSql = sqlCount + " from (" + sql + from + groupBy + ") d" + RegionForBgDao.SQLDataRegion("d");
        Query sqlQuery = getSession().createSQLQuery(resultSql);
        Query countQuery = getSession().createSQLQuery(countSql);
        if (gsId != null) {
            sqlQuery.setLong("gsId", gsId);
            countQuery.setLong("gsId", gsId);
        }
        if (khId != null) {
            sqlQuery.setLong("khId", khId);
            countQuery.setLong("khId", khId);
        }
        sqlQuery.setDate("endDate", endDate);
        countQuery.setDate("endDate", endDate);
        sqlQuery.setFirstResult((pageNo - 1) * pageSize);
        sqlQuery.setMaxResults(pageSize);
        sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Map result = new HashMap();
        result.put("total", countQuery.uniqueResult());
        result.put("rows", sqlQuery.list());
        return result;
    }

    public Map findEjzhYeMx(Long gsId, Date dateStart, Date dateEnd, int pageNo, int pageSize) {
        String sql = "select gs.qc \"gs\", kh.qc \"kh\",ejzh.qc \"ejzh\",ejzh.zh \"zh\",round(sfk.sk,2) \"jfje\",round(sfk.fk,2) \"dfje\",sfk.shrq \"shrq\", " +
                "  round(sum(sfk.sfje) over(order by sfk.shrq),2) \"zhye\",sfk.bm \"bm\",sfk.bz \"bz\",sfk.xsdh \"xsdh\",sfk.mf \"xsgs\", sfk.mf2 \"mf\",sfk.lx \"lx\" ";
        String countSql = "select count(1) ";
        String sumSql = "select sum(sk) \"jfje\",sum(fk) \"dfje\" ";
        String from = "  from (select '' bm,'ysh' zt,:dateStart shrq,'' bz,'' xsdh,'' mf,'' mf2, '结转' lx,:gsId ejzh_id, " +
                "  sum(decode(jfejzh_id - :gsId, 0, sfje, -sfje)) sfje, " +
                "  sum(decode(jfejzh_id - :gsId, 0, sfje, -sfje)) sk, 0 fk,0 ord " +
                "  from t_biz_sfk where (jfejzh_id = :gsId or dfejzh_id = :gsId) " +
                "  and shrq <= :dateStart  and zt = 'ysh' and (sfklx not in (3,4) or sfbzj <> 1)" +
                "        union all " +
                "  select sfk.bm bm,sfk.zt zt,sfk.shrq shrq,sfk.bz || '【' || dx.bz || '】' bz,x.xsdh xsdh,g.jc mf,k.jc mf2," +
                "       (case when sfk.CREATEUSER_ID is null then '线上' else lx.z end ) lx," +
                "       decode(sfk.jfejzh_id - :gsId, 0, sfk.jfejzh_id, sfk.dfejzh_id) ejzh_id," +
                "       decode(sfk.jfejzh_id - :gsId, 0, sfk.sfje, -sfk.sfje) sfje," +
                "       decode(sfk.jfejzh_id - :gsId, 0, sfk.sfje, 0) sk," +
                "       decode(sfk.jfejzh_id - :gsId, 0, 0, sfk.sfje) fk," +
                "       rank() over(order by sfk.shrq desc) ord" +
                "  from t_biz_sfk sfk left join " +
                " (select decode(nvl(sdfk_id, 0), 0, xssk_id, sdfk_id) sfkid, " +
                "       decode(nvl(sdfk_id, 0), 0, xsd_id) xsd_id, " +
                "       decode(nvl(sdfk_id, 0), 0, '销售收款','锁定付款') bz, " +
                "       decode(nvl(xssk_id, 0), 0, order_id) order_id " +
                "  from t_biz_dfkxsd) dx on sfk.id = dx.sfkid " +
                "  left join t_biz_xsd x on (dx.xsd_id = x.id or dx.order_id = x.orderid) and x.deleted = 0 " +
                "  left join t_sys_gsjg g on x.xsgs_id = g.id " +
                "  left join t_sys_gsjg k on x.kh_id = k.id " +
                "  left join t_sys_sjzdz lx on sfk.pjlx_sjzdz_id = lx.id and lx.deleted = 0 " +
                " where (sfk.jfejzh_id = :gsId or sfk.dfejzh_id = :gsId) " +
                "   and sfk.deleted = 0 and sfk.zt = 'ysh' and (sfk.sfklx not in (3,4) or sfk.sfbzj <> 1)" +
                "   and sfk.shrq <= :dateEnd " +
                "   and sfk.shrq > :dateStart) sfk, " +
                "  t_sys_ejzh ejzh,t_sys_gsjg gs,t_sys_gsjg kh " +
                " where ejzh.id = sfk.ejzh_id and ejzh.nbgs_id = gs.id and ejzh.kh_id = kh.id and ejzh.id = :gsId " +
                " order by sfk.ord";
        Query sqlQuery = getSession().createSQLQuery(sql + from);
        Query countQuery = getSession().createSQLQuery(countSql + from);
        Query sumQuery = getSession().createSQLQuery(sumSql + from);
        sqlQuery.setDate("dateStart", dateStart);
        countQuery.setDate("dateStart", dateStart);
        sumQuery.setDate("dateStart", dateStart);
        sqlQuery.setDate("dateEnd", dateEnd);
        countQuery.setDate("dateEnd", dateEnd);
        sumQuery.setDate("dateEnd", dateEnd);
        sqlQuery.setLong("gsId", gsId);
        countQuery.setLong("gsId", gsId);
        sumQuery.setLong("gsId", gsId);
        sqlQuery.setFirstResult((pageNo - 1) * pageSize);
        sqlQuery.setMaxResults(pageSize);
        sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        sumQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Map result = new HashMap();
        result.put("total", countQuery.uniqueResult());
        result.put("rows", sqlQuery.list());
        result.put("footer", sumQuery.list());
        return result;
    }

    //二级账户无结转
    public Map findEjzhYeMxWjz(Long gsId, Date dateStart, Date dateEnd, int pageNo, int pageSize) {
        String sql = "select gs.qc \"gs\", kh.qc \"kh\",ejzh.qc \"ejzh\",ejzh.zh \"zh\",sfk.sk \"jfje\",sfk.fk \"dfje\",sfk.shrq \"shrq\", " +
                "  sum(sfk.sfje) over(order by sfk.shrq) \"zhye\",sfk.bm \"bm\",sfk.bz \"bz\",sfk.xsdh \"xsdh\",sfk.mf \"xsgs\",sfk.lx \"lx\" ";
        String countSql = "select count(1) ";
        String sumSql = "select sum(sk) \"jfje\",sum(fk) \"dfje\" ";
        String from = "  from ( select sfk.bm bm,sfk.zt zt,sfk.shrq shrq,sfk.bz bz,x.xsdh xsdh,g.jc mf,lx.z lx," +
                "       decode(sfk.jfejzh_id - :gsId, 0, sfk.jfejzh_id, sfk.dfejzh_id) ejzh_id," +
                "       decode(sfk.jfejzh_id - :gsId, 0, sfk.sfje, -sfk.sfje) sfje," +
                "       decode(sfk.jfejzh_id - :gsId, 0, sfk.sfje, 0) sk," +
                "       decode(sfk.jfejzh_id - :gsId, 0, 0, sfk.sfje) fk" +
                "  from t_biz_sfk sfk left join t_biz_dfkxsd dx on sfk.id = dx.xssk_id " +
                "  left join t_biz_xsd x on dx.xsd_id = x.id and x.deleted = 0 " +
                "  left join t_sys_gsjg g on x.xsgs_id = g.id " +
                "  left join t_sys_sjzdz lx on sfk.pjlx_sjzdz_id = lx.id and lx.deleted = 0 " +
                " where (sfk.jfejzh_id = :gsId or sfk.dfejzh_id = :gsId) " +
                "   and sfk.deleted = 0 and sfk.zt = 'ysh' " +
                "   and sfk.shrq <= :dateEnd " +
                "   and sfk.shrq > :dateStart) sfk, " +
                "  t_sys_ejzh ejzh,t_sys_gsjg gs,t_sys_gsjg kh " +
                " where ejzh.id = sfk.ejzh_id and ejzh.nbgs_id = gs.id and ejzh.kh_id = kh.id and ejzh.id = :gsId " +
                " order by sfk.shrq";
        Query sqlQuery = getSession().createSQLQuery(sql + from);
        Query countQuery = getSession().createSQLQuery(countSql + from);
        Query sumQuery = getSession().createSQLQuery(sumSql + from);
        sqlQuery.setDate("dateStart", dateStart);
        countQuery.setDate("dateStart", dateStart);
        sumQuery.setDate("dateStart", dateStart);
        sqlQuery.setDate("dateEnd", dateEnd);
        countQuery.setDate("dateEnd", dateEnd);
        sumQuery.setDate("dateEnd", dateEnd);
        sqlQuery.setLong("gsId", gsId);
        countQuery.setLong("gsId", gsId);
        sumQuery.setLong("gsId", gsId);
        sqlQuery.setFirstResult((pageNo - 1) * pageSize);
        sqlQuery.setMaxResults(pageSize);
        sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        sumQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Map result = new HashMap();
        result.put("total", countQuery.uniqueResult());
        result.put("rows", sqlQuery.list());
        result.put("footer", sumQuery.list());
        return result;
    }


    public Map zjrjList(Long gsId, Long khid,Date nowDate, Date endDate, int pageNo, int pageSize) {
        String sql = "select count(1) from t_biz_zjrj t where (t.gsid=? or ?=-1) and  to_char(t.time,'yyyy-MM-dd')=? and t.deleted=0";
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String date = df.format(endDate);
        if (gsId == null)
            gsId = -1L;
        if (khid == null)
            khid = -1L;
        BigDecimal count = (BigDecimal) createSQLQuery(sql, gsId,gsId, date).uniqueResult();
        String sqlColumn = "select " +
                "  yearDate as \"yearDate\",gsid as \"gsid\"," +
                "  gsmc as \"gsmc\",khid as \"khid\"," +
                "  khmc as \"khmc\",ejzhid as \"ejzhid\"," +
                "   ejzhmc as  \"ejzhmc\",zh as \"zh\"," +
                "  zhye as \"zhye\",dfje as \"dfje\"," +
                "  jfje as \"jfje\",bzj as \"bzj\",zhze as \"zhze\", " +
                " createorgan_id as \"createorgan_id\", createdepartment_id as \"createdepartment_id\", " +
                " createuser_id as \"createuser_id\" ";
        if (count.intValue() == 0)
            sql = "select " +
                    "  :date yearDate," +
                    "  gs.id gsid,gs.qc gsmc," +
                    "  kh.id khid,kh.qc khmc," +
                    "  ejzh.id ejzhid,ejzh.qc ejzhmc,ejzh.zh zh," +
                    "  sum(decode(ejzh.id,sfk.jfejzh_id,sfk.sfje,sfk.dfejzh_id,-sfk.sfje,0)) zhye," +
                    "  nvl((select sum(s.sfje) from t_biz_sfk s where s.dfejzh_id = ejzh.id and s.zt = 'ysh' and to_char(s.shrq,'yyyy-MM-dd')=:date),0) dfje," +
                    "  nvl((select sum(s.sfje) from t_biz_sfk s where s.jfejzh_id = ejzh.id and s.zt = 'ysh' and to_char(s.shrq,'yyyy-MM-dd')=:date),0) jfje," +
                    "  nvl((select sum(je) from t_biz_bzj b where b.ejzh_id=ejzh.id and b.sk_id is not null and b.status=1 and b.deleted=0),0) bzj,"+
                    " sum(decode(ejzh.id,sfk.jfejzh_id,sfk.sfje,sfk.dfejzh_id,-sfk.sfje,0))+" +
                    " nvl((select sum(je) from t_biz_bzj b where b.ejzh_id=ejzh.id and b.sk_id is not null and b.status=1 and b.deleted=0),0) zhze, "+
                    " ejzh.createorgan_id createorgan_id, ejzh.createdepartment_id createdepartment_id, ejzh.createuser_id createuser_id "+
                    " from t_sys_ejzh ejzh" +
                    " left join t_biz_sfk sfk" +
                    " on (sfk.jfejzh_id = ejzh.id and sfk.zt = 'ysh' and sfk.deleted = 0)" +
                    " or (sfk.dfejzh_id = ejzh.id and sfk.zt = 'ysh' and sfk.deleted = 0)" +
                    " left join t_sys_gsjg gs on ejzh.nbgs_id = gs.id" +
                    " left join t_sys_gsjg kh on ejzh.kh_id = kh.id" +
                    " where sfk.deleted=0 " +
                    "   and ejzh.deleted=0 " +
                    "   and (sfk.sfklx not in (3,4) or sfk.sfbzj <> 1) "+
                    "   and sfk.sfk.zt='ysh' " +
                    "   and sfk.shrq <=:endDate" +
                    "   and (gs.id=:gsid or :gsid=-1)" +
                    "   and (kh.id=:khid or :khid=-1)" +
                    " group by gs.id,kh.id,ejzh.id, gs.qc, kh.qc, ejzh.qc, " +
                    " ejzh.zh,ejzh.createorgan_id,ejzh.createdepartment_id,ejzh.createuser_id ";
        else
            sql = "select " +
                    "  zjrj.time yearDate,zjrj.gsid gsid," +
                    "  gs.qc gsmc gsmc,zjrj.khid khid," +
                    "  kh.qc khmc khmc,zjrj.ejzhid ejzhid," +
                    "  ejzh.qc  ejzhmc,ejzh.zh zh," +
                    "  zjrj.zhye zhye,zjrj.dfje dfje," +
                    "  zjrj.jeje jfje,zjrj.bzj bzj,zjrj.zhze zhze, " +
                    "zjrj.gsid createorgan_id,  zjrj.createdepartment_id createdepartment_id, zjrj.createuser_id createuser_id "+
                    "from t_biz_zjrj zjrj" +
                    "left join t_sys_gsjg gs on zjrj.gsid = gs.id" +
                    "left join t_sys_gsjg kh on zjrj.khid = kh.id" +
                    "left join t_sys_ejzh ejzh on zjrj.ejzhid = ejzh.id" +
                    "where zjrj.deleted=0" +
                    " and (gs.id=:gsid or :gsid=-1)" +
                    " and (kh.id=:khid or :khid=-1)" +
                    " and zjrj.time=:date";

        String countSql = "select count(1) from (" + sql + ") d " + RegionForBgDao.SQLDataRegion("d");
        String resultSql = sqlColumn + " from (" + sql + ") d" + RegionForBgDao.SQLDataRegion("d");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String nowDateStr = simpleDateFormat.format(nowDate);
        Calendar calendar = GregorianCalendar.getInstance();
        calendar.setTime(endDate);
        calendar.add(Calendar.DATE, 1);
        java.sql.Date paramEndDate = new java.sql.Date(calendar.getTime().getTime());

        SQLQuery query = createSQLQuery(countSql);
        query.setString("date", nowDateStr);
        if (count.intValue() == 0)
            query.setDate("endDate", paramEndDate);
        query.setLong("gsid", gsId);
        query.setLong("khid", khid);
        BigDecimal pageCount = (BigDecimal) query.uniqueResult();

        query = createSQLQuery(resultSql);
        query.setString("date", nowDateStr);
        if (count.intValue() == 0)
            query.setDate("endDate", paramEndDate);
        query.setLong("gsid", gsId);
        query.setLong("khid", khid);
        query.setFirstResult((pageNo - 1) * pageSize);
        query.setMaxResults(pageSize);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List resultList = query.list();

        Map result = new HashMap();
        result.put("total", pageCount);
        result.put("rows", resultList);
        return result;
    }

    /**
     * 盘点某天是否日结，未日结返回true，日结返回false
     *
     * @param gsId
     * @param endDate
     * @return
     */
    public boolean checkZjrj(Long gsId, Date endDate) {
        String sql = "select count(1) from t_biz_zjrj t where t.gsid=? and  to_char(t.time,'yyyyMMdd')=? and t.deleted=0";
        DateFormat df = new SimpleDateFormat("yyyyMMdd");
        String date = df.format(endDate);
        BigDecimal count = (BigDecimal) createSQLQuery(sql, gsId, date).uniqueResult();
        return count.intValue() == 0;
    }

    /**
     * 保存日结
     *
     * @param gsId
     * @param endDate
     * @param ygId
     */
    public void saveZjrj(Long gsId, Date endDate, Long ygId) {
        String sql = "insert into t_biz_zjrj(id,time,gsid,gsmc,khid,khmc,ejzhid,ejzhmc,zh,zhye,dfje,jfje,bzj,zhze,deleted,createuser_id,createdepartment_id ) " +
                " select seq_biz_zjrj.nextval id,x.* from (" +
                "select to_date(:date,'yyyy-MM-dd') time," +
                "  gs.id gsid,gs.qc gsmc," +
                "  kh.id khid,kh.qc khmc," +
                "  ejzh.id ejzhid,ejzh.qc ejzhmc,ejzh.zh zh," +
                "  sum(decode(ejzh.id,sfk.jfejzh_id,sfk.sfje,sfk.dfejzh_id,-sfk.sfje,0)) zhye," +
                "  nvl((select sum(s.sfje) from t_biz_sfk s where s.dfejzh_id = ejzh.id and s.zt = 'ysh' and to_char(s.shrq,'yyyy-MM-dd')=:date),0) dfje," +
                "  nvl((select sum(s.sfje) from t_biz_sfk s where s.jfejzh_id = ejzh.id and s.zt = 'ysh' and to_char(s.shrq,'yyyy-MM-dd')=:date),0) jfje," +
                "  nvl((select sum(je) from t_biz_bzj b where b.ejzh_id=ejzh.id and b.sk_id is not null and b.status=1 and b.deleted=0),0) bzj,"+
                " sum(decode(ejzh.id,sfk.jfejzh_id,sfk.sfje,sfk.dfejzh_id,-sfk.sfje,0))+" +
                " nvl((select sum(je) from t_biz_bzj b where b.ejzh_id=ejzh.id and b.sk_id is not null and b.status=1 and b.deleted=0),0) zhze,"+
                " 0 deleted,:createuserid createuser_id,yg.zgjg_id createdepartment_id " +
                " from t_sys_ejzh ejzh" +
                " left join (select s.* from t_biz_sfk s where not exists(select 1 from t_biz_bzj b where b.sk_id=s.id)) sfk" +
                " on (sfk.jfejzh_id = ejzh.id and sfk.zt = 'ysh' and sfk.deleted = 0)" +
                "  or (sfk.dfejzh_id = ejzh.id and sfk.zt = 'ysh' and sfk.deleted = 0)" +
                " left join t_sys_gsjg gs on ejzh.nbgs_id = gs.id" +
                " left join t_sys_gsjg kh on ejzh.kh_id = kh.id" +
                " left join t_sys_yg yg on yg.id = :createuserid" +
                " left join t_sys_gsjg bm on bm.id = yg.zgjg_id" +
                " where sfk.deleted=0 " +
                "   and ejzh.deleted=0 " +
                "   and sfk.sfk.zt='ysh' " +
                "   and sfk.shrq <=:endDate" +
                "   and (gs.id=:gsid or :gsid=-1)" +
                " group by gs.id,kh.id,ejzh.id, gs.qc, kh.qc, ejzh.qc, ejzh.zh,yg.zgjg_id) x";

        if (gsId == null)
            gsId = -1L;
        Calendar calendar = GregorianCalendar.getInstance();
        calendar.setTime(endDate);
        calendar.add(Calendar.DATE, 1);
        java.sql.Date paramEndDate = new java.sql.Date(calendar.getTime().getTime());
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String paramDateStr = df.format(endDate);

        SQLQuery query = createSQLQuery(sql);
        query.setString("date", paramDateStr);
        query.setDate("endDate", paramEndDate);
        query.setLong("gsid", gsId);
        query.setLong("createuserid", ygId);
        query.executeUpdate();
    }

    /**
     * 删除日结
     *
     * @param gsId
     * @param endDate
     */
    public void deleteZjrj(Long gsId, Date endDate) {
        Long deletedUser = YgService.getCurrentYg().getId();
        String sql = "update t_biz_zjrj t set t.deleted=1 , t.DELETEUSER_ID = ? , t.DATE_DELETED = ?  where t.gsid=?  and to_char(t.time,'yyyyMMdd')=?";
        DateFormat df = new SimpleDateFormat("yyyyMMdd");
        String date = df.format(endDate);
        createSQLQuery(sql, deletedUser,new Date(),gsId, date).executeUpdate();
    }
}
